﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;

using Ledict.Utility.Helper;
using System.Data.SqlClient;
using System.Configuration;
using Ledict.ConditionExtract.Common;

namespace Ledict.ConditionExtract.DAL
{
    public class Express_SourceDAL
    {
        private string DbName;
        public Express_SourceDAL()
        {
            DbName = "JudgeDoc";
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string ccId, string sourceId, string sourceParagraphId, string sourcetexttype, string purpose)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder str = new StringBuilder(@"select count(1) from SYS_Express_Source 
          where CC_ID =@CC_ID and Source_ID =@Source_ID and Source_ParagraphID =@Source_ParagraphID and Source_TextType=@Source_TextType");
            if (!string.IsNullOrEmpty(purpose))
            {
                str.Append("  and ES_Purpose = @ES_Purpose ");
            }


            DbCommand oCmd = oBase.GetSqlStringCommand(str.ToString());

            oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, ccId);
            oBase.AddInParameter(oCmd, "@Source_ID", DbType.String, sourceId);
            oBase.AddInParameter(oCmd, "@Source_ParagraphID", DbType.String, sourceParagraphId);
            oBase.AddInParameter(oCmd, "@ES_Purpose", DbType.String, purpose);
            oBase.AddInParameter(oCmd, "@Source_TextType", DbType.Int32, sourcetexttype);


            if (Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0)
            {
                return true;
            }
            return false;

        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool DelSysExpressSource(string ccId, string sourceId, string sourceParagraphId, string sourcetexttype, string purpose)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder str = new StringBuilder(@"select ES_ID from SYS_Express_Source 
          where CC_ID =@CC_ID and Source_ID =@Source_ID and Source_ParagraphID =@Source_ParagraphID and Source_TextType=@Source_TextType");
            if (!string.IsNullOrEmpty(purpose))
            {
                str.Append("  and ES_Purpose = @ES_Purpose ");
            }


            DbCommand oCmd = oBase.GetSqlStringCommand(str.ToString());

            oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, ccId);
            oBase.AddInParameter(oCmd, "@Source_ID", DbType.String, sourceId);
            oBase.AddInParameter(oCmd, "@Source_ParagraphID", DbType.String, sourceParagraphId);
            oBase.AddInParameter(oCmd, "@ES_Purpose", DbType.String, purpose);
            oBase.AddInParameter(oCmd, "@Source_TextType", DbType.Int32, sourcetexttype);


            if (!string.IsNullOrEmpty(oBase.ExecuteScalar(oCmd).ToString()) )
            {
                string esID = oBase.ExecuteScalar(oCmd).ToString();
                string sql = string.Format(" delete from   SYS_Express_Source where ES_ID='{0}'  ", esID);
                oCmd = oBase.GetSqlStringCommand(sql.ToString());
               int i= oBase.ExecuteNonQuery(oCmd);
                return true;
            }
            return false;

        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        //public string Exists(string ccId, string sourceId, string sourceParagraphId, string sourcetexttype, string purpose, string ccRegexInfoCode, string eiName)
        //{
        //    Database oBase = DatabaseFactory.CreateDatabase(DbName);
        //    StringBuilder str = new StringBuilder(@"select count(1) from SYS_Express_Source 
        //  where CC_ID =@CC_ID and Source_ID =@Source_ID and Source_ParagraphID =@Source_ParagraphID and Source_TextType=@Source_TextType and ES_Purpose = @ES_Purpose ");
        //    if (!string.IsNullOrEmpty(purpose))
        //    {
        //        str.Append("  and ES_Purpose = @ES_Purpose ");
        //    }
        //    str.Append(" and CC_RegexInfoCode=@CC_RegexInfoCode ");
        //    DbCommand oCmd = oBase.GetSqlStringCommand(str.ToString());

        //    oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, ccId);
        //    oBase.AddInParameter(oCmd, "@Source_ID", DbType.String, sourceId);
        //    oBase.AddInParameter(oCmd, "@Source_ParagraphID", DbType.String, sourceParagraphId);
        //    oBase.AddInParameter(oCmd, "@ES_Purpose", DbType.String, purpose);
        //    oBase.AddInParameter(oCmd, "@Source_TextType", DbType.Int32, sourcetexttype);
        //    oBase.AddInParameter(oCmd, "@CC_RegexInfoCode", DbType.String, ccRegexInfoCode);


        //    if (Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0)
        //    {
        //        return true;
        //    }
        //    return false;

        //}

        /// <summary>
        /// 是否存在该记录，通过用途ID
        /// </summary>
        public bool ExistsByPurposeId(string esPurposeId)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(@"select count(1) from SYS_Express_Source 
          where ES_Purpose =@esPurposeId ");

            oBase.AddInParameter(oCmd, "@esPurposeId", DbType.String, esPurposeId);
            if (Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0)
            {
                return true;
            }
            return false;

        }

        /// <summary>
        /// 根据条件，获取记录
        /// </summary>
        public DataTable GetExpressSourceDt(string ccId, string sourceId, string sourceParagraphId, string esPurposeId = null, string sourceTextType = null)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder str = new StringBuilder(@"select * from SYS_Express_Source 
               where CC_ID = @CC_ID and Source_ID = @Source_ID and Source_ParagraphID = @Source_ParagraphID  and Source_TextType = @Source_TextType");
            if (!string.IsNullOrEmpty(esPurposeId))
            {
                str.Append("  and ES_Purpose = @ES_Purpose ");
            }


            DbCommand oCmd = oBase.GetSqlStringCommand(str.ToString());

            oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, ccId);
            oBase.AddInParameter(oCmd, "@Source_ID", DbType.String, sourceId);
            oBase.AddInParameter(oCmd, "@Source_ParagraphID", DbType.String, sourceParagraphId);
            oBase.AddInParameter(oCmd, "@ES_Purpose", DbType.String, esPurposeId);
            oBase.AddInParameter(oCmd, "@Source_TextType", DbType.Int32, sourceTextType);

            return oBase.ExecuteDataSet(oCmd).Tables[0];


        }

        /// <summary>
        /// 添加来源表方法
        /// </summary>
        /// <param name="model">来源表Model</param>
        /// <returns></returns>
        public bool AddSource(SysExpressSource model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SYS_Express_Source(");
            strSql.Append("ES_ID,CC_ID,Source_ID,Source_ParagraphID,Source_Name,Source_ParagraphName,CC_RegexInfoCode,ES_Purpose,Source_TextType,IsML)");
            strSql.Append(" values (");
            strSql.Append("@ES_ID,@CC_ID,@Source_ID,@Source_ParagraphID,@Source_Name,@Source_ParagraphName,@CC_RegexInfoCode,@ES_Purpose,@Source_TextType,@IsML)");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@ES_ID", DbType.String, model.ES_ID);
            oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, model.CC_ID);
            oBase.AddInParameter(oCmd, "@Source_ID", DbType.String, model.Source_ID);
            oBase.AddInParameter(oCmd, "@Source_Name", DbType.String, model.Source_Name);
            oBase.AddInParameter(oCmd, "@Source_ParagraphID", DbType.String, model.Source_ParagraphID);
            oBase.AddInParameter(oCmd, "@Source_ParagraphName", DbType.String, model.Source_ParagraphName);
            oBase.AddInParameter(oCmd, "@CC_RegexInfoCode", DbType.String, model.CC_RegexInfoCode);
            oBase.AddInParameter(oCmd, "@ES_Purpose", DbType.String, model.EsPurposeId);
            oBase.AddInParameter(oCmd, "@Source_TextType", DbType.Int32, model.Source_TextType);
            oBase.AddInParameter(oCmd, "@IsML", DbType.Int32, model.IsML);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Ledict.ConditionExtract.Model.SysExpressSource Model)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update SYS_Express_Source set ");
            strSql.Append("CC_RegexInfoCode=@CC_RegexInfoCode");
            strSql.Append(" where ES_ID=@ES_ID ");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@ES_ID", DbType.String, Model.ES_ID);
            oBase.AddInParameter(oCmd, "@CC_RegexInfoCode", DbType.String, Model.CC_RegexInfoCode);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetSource(string CC_ID)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);

            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetSourceByCC_ID");
            oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, CC_ID);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获得信息来源列表（新）
        /// </summary>
        /// <param name="ccId">情形情节ID</param>
        /// <returns></returns>
        public DataTable GetSource_new(string ccId)
        {
            //SqlConnectionStringBuilder sqlbuilder = new SqlConnectionStringBuilder(ConfigHelper.ConnectionStrings("LdtCommonManage").ConnectionString);
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@"select row_number() over( order by ES_ID )as showindex 
                                        ,ES_ID,CC_ID,Source_ID,Source_ParagraphID, Source_Name,Source_ParagraphName, CC_RegexInfoCode
                                        ,c.AM_Name as ES_Purpose_Name,d.AM_Name as ES_Source_TextType,CASE IsML	WHEN 1 THEN	'是' when 0 then '否' ELSE '' END AS IsML 
                                         FROM SYS_Express_Source as a                                        
                                         left join SYS_Argument as c
                                         on a.ES_Purpose = c.AM_ID
                                         left join (select * from viewSYS_Argument where AT_CODE = 'TextType') as d
                                         on CONVERT(varchar(100),a.Source_TextType) = d.AM_code 
                                         where CC_ID='{0}'", ccId));

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            oBase = DatabaseFactory.CreateDatabase("LdtCommonManage");
            if (otB.Rows.Count > 0)
            {
                otB.Columns.Add("EI_Name");
                otB.Columns.Add("EI_ID");
                for (int i = 0; i < otB.Rows.Count; i++)
                {
                    string code = otB.Rows[i]["CC_RegexInfoCode"].ToString();
                    //select EI_Name, EI_ID from LdtCommonManage.dbo.Reg_InfoUnit where EI_Code = 'EI1909191107550009';
                    //select RIU_CodeCH as EI_Name,RIU_Code as EI_ID from LdtCommonManage.dbo.RuleInfoUnit where RIU_Code = 'EI1909191107550009';
                    string sql = string.Format(" select EI_Name, EI_ID from LdtCommonManage.dbo.Reg_InfoUnit where EI_Code = '{0}' ", code);
                    oCmd = oBase.GetSqlStringCommand(sql.ToString());
                    DataTable otB1 = oBase.ExecuteDataSet(oCmd).Tables[0];
                    if (otB1 != null && otB1.Rows.Count > 0 && !string.IsNullOrEmpty(otB1.Rows[0]["EI_ID"].ToString()))
                    {
                        otB.Rows[i]["EI_ID"] = otB1.Rows[0]["EI_ID"].ToString();
                        otB.Rows[i]["EI_Name"] = otB1.Rows[0]["EI_Name"].ToString();
                    }
                    else
                    {
                        sql = string.Format(" select RIU_CodeCH as EI_Name,RIU_Code as EI_ID from LdtCommonManage.dbo.RuleInfoUnit where RIU_Code = '{0}' ", code);
                        oCmd = oBase.GetSqlStringCommand(sql.ToString());
                        DataTable otB2 = oBase.ExecuteDataSet(oCmd).Tables[0];
                        if (otB2 != null && otB2.Rows.Count > 0 && !string.IsNullOrEmpty(otB2.Rows[0]["EI_ID"].ToString()))
                        {
                            otB.Rows[i]["EI_ID"] = otB2.Rows[0]["EI_ID"].ToString();
                            otB.Rows[i]["EI_Name"] = otB2.Rows[0]["EI_Name"].ToString();
                        }
                    }

                }
                return otB;
            }
            else
            {
                return new DataTable();
            }
        }


        /// <summary>
        /// 获得相关来源信息
        /// </summary>
        public string GetRelationSource(string CC_RegexInfoCode, string ES_ID)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);

            StringBuilder strSql = new StringBuilder();
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetRelationSource");
            oBase.AddInParameter(oCmd, "@CC_RegexInfoCode", DbType.String, CC_RegexInfoCode);
            oBase.AddInParameter(oCmd, "@ES_ID", DbType.String, ES_ID);
            return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));

        }

        public bool DeleteSource(string ES_ID)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from SYS_Express_Source ");
            strSql.Append(" where ES_ID=@ES_ID ");

            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@ES_ID", DbType.String, ES_ID);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }
        public bool DeleteSourceByCC_ID(string CC_ID)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from SYS_Express_Source ");
            strSql.Append(" where CC_ID=@CC_ID ");

            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, CC_ID);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 根据CCID，获取对应节点的用途列表（去重）
        /// </summary>
        /// <param name="ccId">节点ID</param>
        /// <returns></returns>
        public DataTable GetPurposeDtByCcid(string ccId)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@"
                            select distinct  b.AM_ID,b.AM_Name from [SYS_Express_Source] as a
                            inner join [SYS_Argument] as b
                            on a.ES_Purpose = b.AM_ID and a.CC_ID = '{0}'", ccId));

            var ds = oBase.ExecuteDataSet(CommandType.Text, strSql.ToString());
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }

        /// <summary>
        /// 根据CCID和用途ID，获取对应节点的用途列表（去重）
        /// </summary>
        /// <param name="ccId">节点ID</param>
        /// <param name="purposeId">用途ID</param>
        /// <returns></returns>
        public DataTable GetParagraphDtByCcidAndPurpose(string ccId, string purposeId)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder strSql = new StringBuilder();
            strSql.Append(string.Format(@"
                             select * from SYS_Express_Source 
                              where CC_ID = '{0}' and ES_Purpose = '{1}'", ccId, purposeId));

            var ds = oBase.ExecuteDataSet(CommandType.Text, strSql.ToString());
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }

        public bool IsProcess(string ccId)
        {
            if (GetExpressSourceData(ccId).Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取expressSource数据
        /// </summary>
        /// <param name="ccId"></param>
        /// <returns></returns>
        public DataTable GetExpressSourceData(string ccId)
        {
            string strsql = string.Format("select * from SYS_Express_Source where CC_ID = '{0}'", ccId);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            var ds = oBase.ExecuteDataSet(CommandType.Text, strsql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            { return ds.Tables[0]; }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 根据ccid从expressSource表中获取regexInfoCode
        /// </summary>
        /// <param name="ccId"></param>
        /// <returns></returns>
        public string GetRegexInfoCodeByExpressSource(string ccId)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                string strsql = string.Format("select  CC_RegexInfoCode from  SYS_Express_Source where CC_ID in ('{0}')", ccId);
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
               return Convert.ToString(oBase.ExecuteScalar(CommandType.Text, strsql));
                //var ds = oBase.ExecuteDataSet(CommandType.Text, strsql);
                //if (ds != null && ds.Tables[0].Rows.Count > 0)
                //{
                //    foreach (DataRow dr in ds.Tables[0].Rows)
                //    {
                //        sb.Append("'" + dr[0] + "',");
                //    }
                //}
                //return sb.ToString().Remove(sb.ToString().Length - 1, 1);
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        ///  获取ccid所有子节点的regexInfoCode
        /// </summary>
        /// <returns></returns>
        public DataTable GetRegexInfoCodeByCCID(string ccID)
        {
            try
            {
                //先去掉条件and CC_ID<>'{0}'
                string strsql = string.Format(" select distinct CC_RegexInfoCode,CC_ID from  SYS_Express_Source where CC_ID in (select CC_ID FROM SYS_CaseCause where ','+AllParentId+',' like '%,'+'{0}'+',%' )    order by CC_ID  ", ccID);
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
                var ds = oBase.ExecuteDataSet(CommandType.Text, strsql);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                return new DataTable();
            }
        }

        /// <summary>
        ///  获取ccid所有子节点的regexInfoCode
        /// </summary>
        /// <returns></returns>
        public DataTable GetRegexInfoCodeByCCID(string ccID,string eSPurpose,string sourceParagraphID)
        {
            try
            {
                //先去掉条件and CC_ID<>'{0}'
                string strsql = string.Format(" select CC_RegexInfoCode,CC_ID from  SYS_Express_Source where CC_ID in (select CC_ID FROM SYS_CaseCause where ','+AllParentId+',' like '%,'+'{0}'+',%' ) and ES_Purpose='{1}' and Source_ParagraphID='{2}'    order by CC_ID  ", ccID, eSPurpose,sourceParagraphID);
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
                var ds = oBase.ExecuteDataSet(CommandType.Text, strsql);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获取SYS_Express_Source表结构
        /// </summary>
        /// <returns></returns>
        public DataTable GetExpressSourceTableStructs()
        {
            string strsql = "select * from SYS_Express_Source where 1<>1";
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            var ds = oBase.ExecuteDataSet(CommandType.Text, strsql);
            return ds.Tables[0];            
        }

        /// <summary>
        /// 规则共用获取数据
        /// </summary>
        /// <param name="ccids"></param>
        /// <param name="sourceNames"></param>
        /// <returns></returns>
        public DataTable GetExpressData(string ccids, string sourceNames, string ccName)
        {
            string strsql = $" select CC_ID,'{ccName}' as CC_Name, Source_Name,CC_RegexInfoCode as EI_ID,ES_ID,'删除' as ShanChu from SYS_Express_Source where CC_ID in({ccids}) and Source_Name in({sourceNames})  ";
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            var ds = oBase.ExecuteDataSet(CommandType.Text, strsql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 规则共用获取数据
        /// </summary>
        /// <param name="ccids"></param>
        /// <param name="sourceNames"></param>
        /// <returns></returns>
        public DataTable GetExpressDataPL(string ccids, string sourceNames)
        {
            string strsql = $" select b.CC_ID,cc_Name as CC_Name, Source_Name,a.CC_RegexInfoCode as EI_ID,ES_ID,'删除' as ShanChu from SYS_Express_Source a join dbo.SYS_CaseCause b on a.cc_id = b.cc_id where a.CC_ID in ({ccids}) and Source_Name in({sourceNames})  ";
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            var ds = oBase.ExecuteDataSet(CommandType.Text, strsql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 修改公共规则
        /// </summary>
        /// <param name="eiID"></param>
        /// <param name="ccids"></param>
        /// <param name="sourceName"></param>
        /// <returns></returns>
        public bool UpdateDataByCC_ID(string eiID, string ccids, string sourceName)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            string strSql = $"  UPDATE SYS_Express_Source set CC_RegexInfoCode = '{eiID}' where CC_ID in({ccids}) and Source_Name = '{sourceName}' ";

            if (oBase.ExecuteNonQuery(CommandType.Text, strSql) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 修改公共规则为空
        /// </summary>
        /// <param name="eiID"></param>
        /// <param name="ccids"></param>
        /// <param name="sourceName"></param>
        /// <returns></returns>
        public bool UpdateDataByES_ID(string ES_ID)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            string strSql = $"  UPDATE SYS_Express_Source set CC_RegexInfoCode = '' where ES_ID = '{ES_ID}' ";

            if (oBase.ExecuteNonQuery(CommandType.Text, strSql) > 0)
            {
                return true;
            }
            return false;
        }

    }
}
